package test.code;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import net.apexcns.core.BaseVO;
import net.apexcns.core.config.ConfigEnv;
import net.apexcns.core.spring.CoreBeanPropertyRowMapper;
import net.apexcns.core.util.BeanUtil;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;


/**
 *  NamedParameterJdbcDaoSupport 를 활용한 Dao 클래스
 */
@SuppressWarnings("unchecked")
public abstract class CoreDaoSupport extends NamedParameterJdbcDaoSupport {
	protected Logger logger = Logger.getLogger(getClass());
	protected static ConfigEnv env = ConfigEnv.getConfig(ConfigEnv.class);

	public static DataSource newDataSource() {
		return newDataSource(null);
	}

	public static boolean isOracle() {
		return env.getDbDriver().startsWith("oracle");
	}

	private static DriverManagerDataSource dataSource;
	public static DataSource newDataSource(String jndiName) {
		if(dataSource!=null) return dataSource;

		loadJdbcDriver();

		dataSource = new DriverManagerDataSource();
		// TODO: more flexible
		dataSource.setDriverClassName(env.getDbDriver());
		dataSource.setUrl(env.getDbUrl());
		dataSource.setUsername(env.getDbUsername());
		dataSource.setPassword(env.getDbPassword());

		Properties props = new Properties();
		props.setProperty("max-connections", "50");
		props.setProperty("min-connections", "1");
		props.setProperty("inactivity-timeout", "200");
		props.setProperty("wait-timeout", "10");
		dataSource.setConnectionProperties(props);

		return dataSource;
	}
	
	private static void loadJdbcDriver() {
		String driverClassName = isOracle() ? "oracle.jdbc.driver.OracleDriver"
				: "com.mysql.jdbc.Driver";
		try {
			// Class.forName(env.getDbDriver()).newInstance();
			Class.forName(driverClassName).newInstance();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}
	/**
	 * 목록조회
	 */
	private <T, B> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> clazz) {
		List<T> result = null;
		try {
			if(clazz.isAssignableFrom(int.class) || clazz.isAssignableFrom(Integer.class)) {
				result = (List<T>) getNamedParameterJdbcTemplate().queryForList(sql, paramSource, Integer.class);
			} else {
				result = getNamedParameterJdbcTemplate().query(sql, paramSource, new CoreBeanPropertyRowMapper(clazz));
			}
		} catch(EmptyResultDataAccessException e) {
			logger.debug(e.getMessage());
		}
		return result;
	}

	@SuppressWarnings("rawtypes")
	protected <T, B> List<T> queryForList(String sql, B bean, Class<T> clazz) {
		if(bean instanceof Map || bean==null)
			return queryForList(sql, new MapSqlParameterSource((Map) bean), clazz);
		return queryForList(sql, new BeanPropertySqlParameterSource(bean), clazz);
	}

//	protected <T, M extends Map> List<T> queryForList(String sql, M paramMap, Class<T> clazz) {
//		return queryForList(sql, new MapSqlParameterSource(paramMap), clazz);
//	}

	private static Pattern ptnFrom = Pattern.compile("(?mi)(?:\\s+)(FROM)(?:\\s+)");
	protected <T, V extends BaseVO> List<T> queryForPagingMysql(String sql, V vo, Class<T> clazz) {
		List<T> result = null;
		try {
			SqlParameterSource paramSource = new BeanPropertySqlParameterSource(vo);

			String countQry = null;
			Matcher m = ptnFrom.matcher(sql);
			if(m.find()) {
				int end = m.end(1);
				countQry = "\n SELECT COUNT(*) FROM" + sql.substring(end);
			} else {
				countQry = "\n SELECT COUNT(*) FROM (" + sql + ") T1";
			}
			int pageRowCount = getNamedParameterJdbcTemplate().queryForInt(countQry, paramSource);
			vo.setPageRowCount(pageRowCount);
			if(pageRowCount==0) return result;

			String pagingQry = sql + "\n LIMIT " + (vo.getPageSize()*(vo.getPageNumber()-1)) + ", " + vo.getPageSize();
			result = getNamedParameterJdbcTemplate().query(pagingQry, paramSource, new CoreBeanPropertyRowMapper(clazz));
		} catch(EmptyResultDataAccessException e) {
			logger.debug(e.getMessage());
		}
		return result;
	}

	protected <T, V extends BaseVO> List<T> queryForPaging(String sql, V vo, Class<T> clazz) {
		return queryForPagingMysql(sql, vo, clazz);
	}

	private <T> T queryFor(String sql, SqlParameterSource paramSource, Class<T> clazz) {
		T result = null;
		try {
			if(clazz.isAssignableFrom(int.class) || clazz.isAssignableFrom(Integer.class)) {
				result = (T) new Integer(getNamedParameterJdbcTemplate().queryForInt(sql, paramSource));
			} else if(clazz.isAssignableFrom(long.class) || clazz.isAssignableFrom(Long.class)) {
				result = (T) new Long(getNamedParameterJdbcTemplate().queryForLong(sql, paramSource));
			} else if(clazz.isAssignableFrom(String.class)) {
				result = (T) getNamedParameterJdbcTemplate().queryForObject(sql, paramSource, clazz);
			} else if(clazz.isAssignableFrom(Map.class)) {
				result = (T) getNamedParameterJdbcTemplate().queryForMap(sql, paramSource);
			} else {
				result = (T) getNamedParameterJdbcTemplate().queryForObject(sql, paramSource, new CoreBeanPropertyRowMapper(clazz));
			}
		} catch(EmptyResultDataAccessException e) {
			logger.debug(e.getMessage());
		}
		return result;
	}

	// TODO untested
	@SuppressWarnings({ "null", "rawtypes" })
	protected <B> B queryFor(String sql, B bean) {
		Class clazz = null;
		try {
			clazz = bean==null ? clazz.newInstance().getClass() : bean.getClass();
		} catch (Exception e) {
			logger.warn(e.getMessage());
		}
		if(bean instanceof Map)
			return (B) queryFor(sql, new MapSqlParameterSource((Map) bean), clazz);
		if(bean instanceof Map || bean==null)
			return (B) queryFor(sql, new MapSqlParameterSource((Map) bean), clazz);
		return (B) queryFor(sql, new BeanPropertySqlParameterSource(bean), clazz);
	}

	@SuppressWarnings("rawtypes")
	protected <T, B> T queryFor(String sql, B bean, Class<T> clazz) {
		if(bean instanceof Map || bean==null)
			return queryFor(sql, new MapSqlParameterSource((Map) bean), clazz);
		return queryFor(sql, new BeanPropertySqlParameterSource(bean), clazz);
	}

	protected <B> int updateForKey(String sql, B bean, String propetyName) {
		int result = 0;
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(bean);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		result = getNamedParameterJdbcTemplate().update(sql, paramSource, keyHolder);
		try {
			BeanUtils.setProperty(bean, propetyName, keyHolder.getKey());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return result;
	}

	// fix for oracle bug
	protected <B> int updateForKeyOracle(String sql, B bean, String propetyName, String seqName) {
		int result = 0;
		try {
			String seqQry = "\n SELECT " + seqName + ".nextval FROM DUAL";
			int genKey = getNamedParameterJdbcTemplate().queryForInt(seqQry, (SqlParameterSource) null);
			BeanUtils.setProperty(bean, propetyName, genKey);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(bean);
		result = getNamedParameterJdbcTemplate().update(sql, paramSource);
		return result;
	}
	
	private <B> int updateFor(String sql, SqlParameterSource paramSource) {
		int result = 0;
		try {
			result = getNamedParameterJdbcTemplate().update(sql, paramSource);
		} catch(EmptyResultDataAccessException e) {
			logger.debug(e.getMessage());
		}
		return result;
	}

	@SuppressWarnings("rawtypes")
	protected <B> int updateFor(String sql, B bean) {
		if(bean instanceof Map || bean==null)
			return updateFor(sql, new MapSqlParameterSource((Map) bean));
		return updateFor(sql, new BeanPropertySqlParameterSource(bean));
	}

	protected <B> int updateFor(String sql, B ... beans) {
		return updateFor(sql, BeanUtil.mergeToMap(beans));
	}

	@SuppressWarnings("rawtypes")
	protected <B> Map queryForCodeMap(String sql, B bean) {
		if(bean instanceof Map || bean==null)
			return queryForCodeMap(sql, new MapSqlParameterSource((Map) bean));
		return queryForCodeMap(sql, new BeanPropertySqlParameterSource(bean));
	}

	@SuppressWarnings("rawtypes")
	protected Map queryForCodeMap(String sql, SqlParameterSource paramSource) {
		return (Map) getNamedParameterJdbcTemplate().query(sql, paramSource, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map resultsMap = new LinkedHashMap();
				while (rs.next()) {
					resultsMap.put(rs.getString(1), rs.getString(2));
				}
				return resultsMap;
			}
		});
	}

	protected <B> int[] batchUpdate(String sql, List<B> beanList) {
		return batchUpdate(sql, beanList.toArray(new Object[0]));
	}
	@SuppressWarnings("rawtypes")
	protected int[] batchUpdate(String sql, Object ... beans) {
		SqlParameterSource[] batchArgs = new SqlParameterSource[beans.length];
		for(int i=0; i<beans.length; i++) {
			Object bean = beans[i];
			batchArgs[i] = (bean instanceof Map || bean==null)
				? new MapSqlParameterSource((Map) bean) : new BeanPropertySqlParameterSource(bean);
		}
		return batchUpdate(sql, batchArgs);
	}

	protected int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {
		return doExecuteBatchUpdateWithNamedParameters(sql, batchArgs);
	}

	private int[] doExecuteBatchUpdateWithNamedParameters(String sql,
			final SqlParameterSource[] batchArgs) {
		if (batchArgs.length <= 0) {
			return new int[] {0};
		}
		final ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
		String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, batchArgs[0]);
		return getNamedParameterJdbcTemplate().getJdbcOperations().batchUpdate(
				sqlToUse, new BatchPreparedStatementSetter() {

					public void setValues(PreparedStatement ps, int i) throws SQLException {
						Object[] values = NamedParameterUtils.buildValueArray(parsedSql, batchArgs[i], null);
						int[] columnTypes = NamedParameterUtils.buildSqlTypeArray(parsedSql, batchArgs[i]);
						doSetStatementParameters(values, ps, columnTypes);
					}

					public int getBatchSize() {
						return batchArgs.length;
					}
				});
	}

	private void doSetStatementParameters(Object[] values,
			PreparedStatement ps, int[] columnTypes) throws SQLException {
		int colIndex = 0;
		for (Object value : values) {
			colIndex++;
			if (value instanceof SqlParameterValue) {
				SqlParameterValue paramValue = (SqlParameterValue) value;
				StatementCreatorUtils.setParameterValue(ps, colIndex, paramValue, paramValue.getValue());
			} else {
				int colType;
				if (columnTypes == null || columnTypes.length < colIndex) {
					colType = SqlTypeValue.TYPE_UNKNOWN;
				} else {
					colType = columnTypes[colIndex - 1];
				}
				StatementCreatorUtils.setParameterValue(ps, colIndex, colType, value);
			}
		}
	}

	private String getQueryString(String sql, SqlParameterSource paramSource) {
		ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
		String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
		Object params[] = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
		int types[] = NamedParameterUtils.buildSqlTypeArray(parsedSql, paramSource);

		StringBuffer buf = new StringBuffer();
		try {
			Scanner scan = new Scanner(sqlToUse + " ");
			scan.useDelimiter("\\?");
	
			int idx = 0;
			while(scan.hasNext()) {
				buf.append(scan.next());
				if(idx<params.length) {
					if(types[idx]==Types.VARCHAR || types[idx]==Types.DATE
							|| params[idx] instanceof String || params[idx] instanceof Date) {
						buf.append("'" + params[idx++] + "'");
					} else {
						buf.append(params[idx++]);
					}
				}
			}
		} catch(Exception e) {
			logger.warn(e.getMessage());
		}
		return buf.toString();
	}

	protected <B> void debugQueryString(String sql, B bean) {
		if(logger.isDebugEnabled()) logger.debug(getQueryString(sql, bean));
	}

	@SuppressWarnings("rawtypes")
	protected <B> String getQueryString(String sql, B bean) {
		if(bean instanceof Map || bean==null)
			return getQueryString(sql, new MapSqlParameterSource((Map) bean));
		return getQueryString(sql, new BeanPropertySqlParameterSource(bean));
	}

	protected String makeQueryIn(int[] keys) {
		if(keys==null || keys.length<1) return null;
		StringBuilder strb = new StringBuilder();
		for(int i=0; i<keys.length; i++) {
			if(i>0) strb.append(",");
			strb.append("").append(keys[i]).append("");	
		}
		return strb.toString();
	}
}
